package org.skydiveapplibrary.repository;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.skydiveapplibrary.data.Database;
import org.skydiveapplibrary.data.Rig;
import org.skydiveapplibrary.data.RigComponent;
import org.skydiveapplibrary.data.RigReminder;
import org.skydiveapplibrary.data.TimeUnit;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class RigRepository
{
	// jump count query format
	private static final String JUMP_COUNT_QUERY_FORMAT =
		"select count(*) " +
		"from log_entry " +
		"inner join log_entry_rig on log_entry._id = log_entry_rig.log_entry " +
		"where log_entry_rig.rig = {0}";
	
	private Database database;
	
	public RigRepository(Context context)
	{
		database = new Database(context);
	}
	
	public List<Rig> getRigs()
	{
		return getRigs(false, false);
	}
	
	public List<Rig> getArchivedRigs()
	{
		return getRigs(false, true);
	}
	
	public List<Rig> getAllRigs()
	{
		return getRigs(true, false);
	}
	
	private List<Rig> getRigs(boolean allRigs, boolean archived)
	{
		List<Rig> rigs = new ArrayList<Rig>();
		
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			// build where clause
			StringBuilder whereClause = new StringBuilder();
			if (!allRigs)
			{
				whereClause.append(Database.RIG_ACTIVE + " = " + 1);
				whereClause.append(" and ");
				whereClause.append(Database.RIG_ARCHIVED + " = " + (archived ? 1 : 0));
			}
			
			// perform query
			cursor = db.query(
					Database.RIG_TABLE,
					Database.RIG_ALL_COLUMNS,
					whereClause.toString(),
					null, null, null,
					Database.RIG_NAME);
			
			// iterate over results
			while (cursor.moveToNext())
			{
				// create rig
				Rig rig = createRig(cursor);
				rigs.add(rig);
				
				// get components
				rig.getComponents().addAll(getComponents(rig.getId(), db));
				// get reminders
				rig.getReminders().addAll(getReminders(rig.getId(), db));
				// get jump count
				rig.setJumpCount(getJumpCount(rig.getId(), db));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
		
		return rigs;
	}
	
	public List<Rig> getRigs(List<Integer> ids)
	{
		List<Rig> rigs = new ArrayList<Rig>();
		
		SQLiteDatabase db = database.getReadableDatabase();
		try
		{
			for (int id: ids)
			{
				rigs.add(getRig(id, db));
			}
		}
		finally
		{
			db.close();
		}
		
		return rigs;
	}
	
	public Rig findRig(String name)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		try
		{
			return getRig(db, Database.RIG_NAME + " = ?", name);
		}
		finally
		{
			db.close();
		}
	}
	
	public Rig getFromUniqueId(String uniqueId)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		try
		{
			return getRig(db, Database.RIG_UNIQUE_ID + " = ?", uniqueId);
		}
		finally
		{
			db.close();
		}
	}
	
	public Rig getRig(int id)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		try
		{
			return getRig(id, db);
		}
		finally
		{
			db.close();
		}
	}
	
	public Rig getRig(int id, SQLiteDatabase db)
	{
		return getRig(db, Database.RIG_ID + " = ?", String.valueOf(id));
	}
	
	private Rig getRig(SQLiteDatabase db, String where, String... whereParams)
	{
		Rig rig = null;
		
		// perform query
		Cursor cursor = null;
		try
		{
			cursor = db.query(
					Database.RIG_TABLE,
					Database.RIG_ALL_COLUMNS,
					where,
					whereParams,
					null,
					null,
					null);
			
			if (cursor.moveToFirst())
			{
				// create rig
				rig = createRig(cursor);
				
				// get jump count
				rig.setJumpCount(getJumpCount(rig.getId(), db));
				
				// get components
				rig.getComponents().addAll(getComponents(rig.getId(), db));
				
				// get reminders
				rig.getReminders().addAll(getReminders(rig.getId(), db));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
		
		return rig;
	}
	
	public List<Rig> getPrimaryRigs()
	{
		List<Rig> rigs = new ArrayList<Rig>();
		
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			// perform query
			cursor = db.query(
					Database.RIG_TABLE,
					Database.RIG_ALL_COLUMNS,
					Database.RIG_PRIMARY + " = " + 1,
					null, null, null, null);
			
			while (cursor.moveToNext())
			{
				// create and add rig
				rigs.add(createRig(cursor));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
		
		return rigs;
	}
	
	public void addRig(Rig rig)
	{
		// set modified
		if (rig.getLastModifiedUtc() == null)
		{
			rig.setLastModifiedUtc(new Date(System.currentTimeMillis()));
		}
		
		//set uniqueId
		if (rig.getUniqueId() == null || rig.getUniqueId() == "")
		{
			rig.setUniqueId(UUID.randomUUID().toString());
		}
		
		// get insert values
		ContentValues values = createContentValues(rig);
		
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// insert rig
			long rigId = db.insert(Database.RIG_TABLE, null, values);
			// update rig
			rig.setId((int)rigId);
			
			// insert components
			addComponents(rig.getId(), rig.getComponents(), db);
			
			// insert reminders
			addReminders(rig.getId(), rig.getReminders(), db);
						
			// set tx success
			db.setTransactionSuccessful();
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	public void updateRig(Rig rig)
	{
		updateRig(rig, true);
	}
	
	public void updateRig(Rig rig, boolean updateLastModified)
	{
		// set modified
		if (updateLastModified)
		{
			rig.setLastModifiedUtc(new Date(System.currentTimeMillis()));
		}
		
		// get update values
		ContentValues values = createContentValues(rig);
		
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// update rig
			db.update(Database.RIG_TABLE,
					values,
					Database.RIG_ID + " = " + rig.getId(),
					null);
			
			// delete all existing components
			deleteComponents(rig.getId(), db);
			
			// insert components
			addComponents(rig.getId(), rig.getComponents(), db);
			
			// delete all existing reminders
			deleteReminders(rig.getId(), db);
			
			// insert reminders
			addReminders(rig.getId(), rig.getReminders(), db);
			
			// set tx success
			db.setTransactionSuccessful();
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	public void deleteRig(Rig rig)
	{
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// delete components
			deleteComponents(rig.getId(), db);
			
			// delete reminders
			deleteReminders(rig.getId(), db);
			
			// create update values
			ContentValues values = new ContentValues();
			values.put(Database.RIG_ACTIVE, false);
					
			// deactivate rig
			db.update(
					Database.RIG_TABLE,
					values,
					Database.RIG_ID + " = " + rig.getId(),
					null);
			
			// set tx success
			db.setTransactionSuccessful();
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	private int getJumpCount(int rigId, SQLiteDatabase db)
	{
		// construct query
		String query = MessageFormat.format(JUMP_COUNT_QUERY_FORMAT, rigId);
		// result
		int result = 0;
		
		// execute query
		Cursor cursor = null;
		try
		{
			cursor = db.rawQuery(query, null);
			
			// get results
			if (cursor.moveToFirst())
			{
				result = cursor.getInt(0);
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
		return result;
	}
	
	private List<RigComponent> getComponents(int rigId, SQLiteDatabase db)
	{
		List<RigComponent> components = new ArrayList<RigComponent>();
		
		// exec get
		Cursor cursor = null;
		try
		{
			cursor = db.query(
				Database.RIG_COMPONENT_TABLE,
				Database.RIG_COMPONENT_ALL_COLUMNS,
				Database.RIG_COMPONENT_RIG + " = " + rigId,
				null, null, null, null);
			
			// iterate over results
			while (cursor.moveToNext())
			{
				RigComponent component = createComponent(cursor);
				components.add(component);
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}

		return components;
	}
	
	private List<RigReminder> getReminders(int rigId, SQLiteDatabase db)
	{
		List<RigReminder> reminders = new ArrayList<RigReminder>();
		
		// exec get
		Cursor cursor = null;
		try
		{
			cursor = db.query(
				Database.RIG_REMINDER_TABLE,
				Database.RIG_REMINDER_ALL_COLUMNS,
				Database.RIG_REMINDER_RIG + " = " + rigId,
				null, null, null, null);
			
			// iterate over results
			while (cursor.moveToNext())
			{
				RigReminder reminder = createReminder(cursor);
				reminders.add(reminder);
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}

		return reminders;
	}
	
	private void addComponents(int rigId, List<RigComponent> components, SQLiteDatabase db)
	{
		ContentValues values;
		for (RigComponent component: components)
		{
			// get values to insert
			values = createContentValues(rigId, component);
			
			// do insert
			db.insert(
					Database.RIG_COMPONENT_TABLE,
					null,
					values);
		}
	}
	
	private void addReminders(int rigId, List<RigReminder> reminders, SQLiteDatabase db)
	{
		ContentValues values;
		for (RigReminder reminder: reminders)
		{
			// get values to insert
			values = createContentValues(rigId, reminder);
			
			// do insert
			db.insert(
					Database.RIG_REMINDER_TABLE,
					null,
					values);
		}
	}
	

	private void deleteComponents(int rigId, SQLiteDatabase db)
	{
		db.delete(
				Database.RIG_COMPONENT_TABLE,
				Database.RIG_COMPONENT_RIG + " = " + rigId,
				null);
	}

	private void deleteReminders(int rigId, SQLiteDatabase db)
	{
		db.delete(
				Database.RIG_REMINDER_TABLE,
				Database.RIG_REMINDER_RIG + " = " + rigId,
				null);
	}

	private Rig createRig(Cursor cursor)
	{
		Rig rig = new Rig();
		
		rig.setId(Database.getInteger(cursor, Database.RIG_ID));
		rig.setActive(Database.getBoolean(cursor, Database.RIG_ACTIVE));
		rig.setName(Database.getString(cursor, Database.RIG_NAME));
		rig.setPrimary(Database.getBoolean(cursor, Database.RIG_PRIMARY));
		rig.setArchived(Database.getBoolean(cursor, Database.RIG_ARCHIVED));
		rig.setNotes(Database.getString(cursor, Database.RIG_NOTES));
		rig.setLastModifiedUtc(Database.getDateTime(cursor, Database.RIG_LAST_MODIFIED_UTC));
		rig.setUniqueId(Database.getString(cursor, Database.RIG_UNIQUE_ID));
		
		return rig;
	}
	
	private RigComponent createComponent(Cursor cursor)
	{
		RigComponent component = new RigComponent();
		
		component.setId(Database.getInteger(cursor, Database.RIG_COMPONENT_ID));
		component.setName(Database.getString(cursor, Database.RIG_COMPONENT_NAME));
		component.setSerialNumber(Database.getString(cursor, Database.RIG_COMPONENT_SERIAL_NUMBER));
		component.setNotes(Database.getString(cursor, Database.RIG_COMPONENT_NOTES));
		
		return component;
	}
	
	private RigReminder createReminder(Cursor cursor)
	{
		RigReminder reminder = new RigReminder();
		
		reminder.setId(Database.getInteger(cursor, Database.RIG_REMINDER_ID));
		reminder.setName(Database.getString(cursor, Database.RIG_REMINDER_NAME));
		reminder.setLastCompletedDate(Database.getDate(cursor, Database.RIG_REMINDER_LAST_COMPLETED_DATE));
		reminder.setInterval(Database.getInteger(cursor, Database.RIG_REMINDER_INTERVAL));
		reminder.setIntervalUnit(TimeUnit.valueOf(
				Database.getString(cursor, Database.RIG_REMINDER_INTERVAL_UNIT)));
		
		return reminder;
	}
	
	private ContentValues createContentValues(Rig rig)
	{
		ContentValues values = new ContentValues();
		
		values.put(Database.RIG_ACTIVE, rig.isActive());
		values.put(Database.RIG_NAME, rig.getName());
		values.put(Database.RIG_PRIMARY, rig.isPrimary());
		values.put(Database.RIG_ARCHIVED, rig.isArchived());
		values.put(Database.RIG_NOTES, rig.getNotes());
		values.put(Database.RIG_LAST_MODIFIED_UTC, Database.dateTimeToString(rig.getLastModifiedUtc()));
		values.put(Database.RIG_UNIQUE_ID, rig.getUniqueId());
		
		return values;
	}
	
	private ContentValues createContentValues(int rigId, RigComponent component)
	{
		ContentValues values = new ContentValues();
		
		values.put(Database.RIG_COMPONENT_RIG, rigId);
		values.put(Database.RIG_COMPONENT_NAME, component.getName());
		values.put(Database.RIG_COMPONENT_SERIAL_NUMBER, component.getSerialNumber());
		values.put(Database.RIG_COMPONENT_NOTES, component.getNotes());
		
		return values;
	}
	
	private ContentValues createContentValues(int rigId, RigReminder reminder)
	{
		ContentValues values = new ContentValues();
		
		values.put(Database.RIG_REMINDER_RIG, rigId);
		values.put(Database.RIG_REMINDER_NAME, reminder.getName());
		values.put(Database.RIG_REMINDER_LAST_COMPLETED_DATE, Database.dateToString(reminder.getLastCompletedDate()));
		values.put(Database.RIG_REMINDER_INTERVAL, reminder.getInterval());
		values.put(Database.RIG_REMINDER_INTERVAL_UNIT, reminder.getIntervalUnit().toString());
		
		return values;
	}
	
	public boolean exists(String where, String... whereParams)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			// perform query
			cursor = db.query(
					Database.RIG_TABLE,
					Database.RIG_ALL_COLUMNS,
					where + " = ?",
					whereParams,
					null,
					null,
					null);
			
			return cursor.getCount() > 0;
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
	}
}